package com.apestech.framework.db.session;

import com.apestech.api.tables.SequenceManage;
import com.apestech.framework.db.SqlResultSet;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.db.SqlTemplate;
import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.db.callback.TableJsonCallBackHandler;
import com.apestech.framework.dictionary.modul.ImportedKey;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.exec.MasterTableExec;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.sql.lib.TableSql;
import com.apestech.framework.sql.lib.TableOper;
import com.apestech.framework.sql.table.MasterTableDelete;
import com.apestech.framework.sql.table.MasterTableQuery;
import com.apestech.framework.sql.table.MasterTableSave;
import com.apestech.framework.util.LocalVariable;
import com.apestech.framework.util.callback.IdKeyCallback;
import com.apestech.framework.xml.pub.ReadXml;
import com.apestech.framework.xml.pub.core.NodeJson;
import com.apestech.framework.xml.table.modul.Tables;

import java.util.ArrayList;

/**
 * @author hup
 * @create 2018-08-31-13:15
 */
public class ForDDL extends SessionUtil{
    private SqlTemplate cn;
    private SqlSession session;

    public ForDDL(SqlSession session){
        this.session=session;
        this.cn=session.getCn();
    }

    public void executeDDL(String sql) throws Exception{
        if(sql==null || "".equals(sql)) return;
        System.out.println(sql+";");
        this.cn.executeDDL(sql);
    }

    public void executeDDL(ArrayList<String> sqlList) throws Exception{
        int len=sqlList.size();
        for(int i=0; i<len; i++){
            String sql=sqlList.get(i);
            executeDDL(sql);
        }
    }

    public void renameTable(String tableId, String nTableId) throws Exception {
        Table tab=this.session.getDictTable(tableId);
        if(tab==null) return;
        ArrayList<String> sqlList=new ArrayList<String>();
        sqlList.add("ALTER TABLE "+tableId+" RENAME TO "+nTableId);
        if(tab.getPkKey().getPkName().equals("PK_"+tableId)){
            sqlList.add("ALTER TABLE "+nTableId+" DROP CONSTRAINT PK_"+tableId+" CASCADE");
            String fk=tab.getPkKey().getPkColumn();
            if(fk!=null) fk=fk.replace(";",",");
            sqlList.add("ALTER TABLE "+nTableId+" ADD CONSTRAINT PK_"+nTableId+" PRIMARY KEY ("+fk+")");
        }
        executeDDL(sqlList);
    }

    public void dropTable(String tableName) throws Exception {
        TableSql ddl=new TableSql(session);
        executeDDL(ddl.dropTable(tableName));
    }

    public void createTable(String tableId) throws Exception {
        TableSql ddl=getTablesSql(tableId, session);
        createTable(ddl);
    }

    public void createTable(Tables tables) throws Exception {
        TableSql ddl=getTablesSql(tables, session);
        createTable(ddl);
    }

    public void createTableSql(String tableId) throws Exception {
        TableSql ddl=getTablesSql(tableId, session);
        createTable_sql(ddl);
    }

    public void createTableSql(Tables tables) throws Exception {
        TableSql ddl=getTablesSql(tables, session);
        createTable_sql(ddl);
    }

    private void createTable(TableSql ddl) throws Exception {
        ddl.getCreateSql();
        executeDDL(ddl.getSql());
        if(ddl.getTable().getSeq()!=null){
            SequenceManage sequenceManage=new SequenceManage(session);
            sequenceManage.createSeq(ddl.getTable().getSeq(), true);
        }
    }

    private void createTable_sql(TableSql ddl) throws Exception {
        ddl.getCreateSql();
        printSqlList(ddl.getSql());
        if(ddl.getTable().getSeq()!=null){
            SequenceManage sequenceManage=new SequenceManage(session);
            sequenceManage.createSeq(ddl.getTable().getSeq(), false);
        }
    }

    public void createTable(String tableName, JSONArray fields) throws Exception {
        Tables table=new Tables();
        JSONObject m=new JSONObject();
        m.put("tabName", tableName);
        m.put("tabComment", tableName);
        table.addTabInfo(tableName, m);
        table.addMasterTab(tableName, tableName, "1","1");
        int len=fields.size();
        for(int i=0; i<len; i++) table.addField(tableName, fields.get(i));
        createTable(table);
    }

    // 导入数据以时间戳上的数据为时间判断点来依次核查数据是否存在错导、漏导、重复导
    public void createTableFromTable(String stableName, String dtableName) throws Exception{
        TableOper oper=new TableOper(session, stableName, dtableName);
        createTable(oper.getTableFromTable());
    }

    public void createImportTableFromTable(String stableName, String dtableName) throws Exception{
        TableOper oper=new TableOper(session, stableName, dtableName);
        createTable(oper.getImportTableFromTable());
    }

    public void createStopTableFromTable(String stableName, String dtableName) throws Exception{
        TableOper oper=new TableOper(session, stableName, dtableName);
        createTable(oper.getStopTableFromTable());
    }

    public JSONObject getIndexInfo(String tableName) throws Exception {
        return this.cn.getIndexInfo(tableName);
    }

    public void dropTables(String tableId) throws Exception {
        TableSql ddl=getTablesSql(tableId, session);
        executeDDL(ddl.dropTable(session));
    }

    public void dropTables(Tables tables) throws Exception {
        TableSql ddl=getTablesSql(tables, session);
        executeDDL(ddl.dropTable(session));
    }

    public void dropTablesSql(String tableId) throws Exception {
        TableSql ddl=getTablesSql(tableId, session);
        printSqlList(ddl.dropTable(session));
    }

    public void dropTablesSql(Tables tables) throws Exception {
        TableSql ddl=getTablesSql(tables, session);
        printSqlList(ddl.dropTable(session));
    }

    public void deleteTables(String tableId) throws Exception {
        TableSql ddl=getTablesSql(tableId, session);
        executeDDL(ddl.deleteTable());
    }

    public JSONArray queryMasterTable(String tableId, JSONObject map) throws Exception {
        MasterTableQuery table=new MasterTableQuery(session, getTablesSql(tableId, session), map);
        table.query();
        return table.getQlist();
    }

    public JSONArray queryMasterTable(Tables tables, JSONObject map) throws Exception {
        MasterTableQuery table=new MasterTableQuery(session, getTablesSql(tables, session), map);
        table.query();
        return table.getQlist();
    }

    public JSONArray queryMasterTable(Tables tables, JSONObject map, TableJsonCallBackHandler tableJsonCallBackHandler) throws Exception {
        MasterTableQuery table=new MasterTableQuery(session, getTablesSql(tables, session), map);
        table.setTableJsonCallBackHandler(tableJsonCallBackHandler);
        table.query();
        return table.getQlist();
    }

    public void deleteMasterTable(String tableId, JSONObject map) throws Exception {
        MasterTableDelete table=new MasterTableDelete(session, getTablesSql(tableId, session), map);
        table.delete();
        //System.out.println(JSON.toJSONString(table.getKeymap()));
    }

    public void deleteMasterTable(Tables tables, JSONObject map) throws Exception {
        MasterTableDelete table=new MasterTableDelete(session, getTablesSql(tables, session), map);
        table.delete();
    }

    public void getDataXml(String tableId) throws Exception {
        MasterTableExec exec=new MasterTableExec(getTablesSql(tableId, session).getTable(), session);
        exec.getDataXml();
    }

    public void getDataXml(Tables tables) throws Exception {
        MasterTableExec exec=new MasterTableExec(getTablesSql(tables, session).getTable(), session);
        exec.getDataXml();
    }

    public Object saveMasterTable(String tableId) throws Exception {
        return saveMasterTable(getTablesSql(tableId, session));
    }

    public Object saveMasterTable(Tables tables) throws Exception {
        return saveMasterTable(getTablesSql(tables, session));
    }

    public Object saveMasterTable(Tables tables, JSONObject map) throws Exception {
        return saveMasterTableData(getTablesSql(tables, session), map, null);
    }

    public Object saveMasterTable(Tables tables, JSONObject map, IdKeyCallback idKeyCallback) throws Exception {
        return saveMasterTableData(getTablesSql(tables, session), map, idKeyCallback);
    }

    private Object saveMasterTable(TableSql ddl) throws Exception {
        ReadXml read=new ReadXml();
        read.loadXmlFromFile(LocalVariable.getTempDir()+"data_man\\"+ddl.getTable().getMasterTable()+".xml");
        NodeJson json=new NodeJson(read.getRoot());
        json.expain();
        JSONObject map=json.getResult();
        return saveMasterTableData(ddl, map, null);
    }

    private Object saveMasterTableData(TableSql ddl, JSONObject map, IdKeyCallback idKeyCallback) throws Exception {
        MasterTableSave table=new MasterTableSave(session, getTablesSql(ddl.getTable(), session), map);
        table.setIdKeyCallback(idKeyCallback);
        return table.save();
    }

    public void alterTableColumn(String table, String field, String type) throws Exception {
        TableSql ddl=new TableSql(session);
        executeDDL(ddl.getAlterColumnType(table, field, type));
    }

    public void getAlterAddColumn(String table, String field, String type, String comment) throws Exception {
        TableSql ddl=new TableSql(session);
        executeDDL(ddl.getAlterAddColumn(table, field, type, comment));
    }

    public void tableAddColumn(String tabname, JSONArray fields) throws Exception {
        TableSql ddl=new TableSql(session);
        executeDDL(ddl.getAddColumn(tabname, fields));
    }

    public void enableTableFk(String tabname) throws Exception {
        ArrayList<String> list=new ArrayList<String>();
        Table tab=session.getDictTable(tabname);
        ImportedKey imk=tab.getPkFKey();
        if(imk==null) return;
        JSONObject field=imk.getField();
        ArrayList<String> klist=field.getKeylist();
        int len=klist.size();
        for(int i=0; i<len; i++){
            ImportedKey.ImportedField f=imk.get(klist.get(i));
            list.add("ALTER TABLE "+tabname+" ENABLE CONSTRAINT "+f.getFkeyName());
        }
        if(len>0) executeDDL(list);
    }

    public void disableTableFk(String tabname) throws Exception {
        ArrayList<String> list=new ArrayList<String>();
        Table tab=session.getDictTable(tabname);
        ImportedKey imk=tab.getPkFKey();
        if(imk==null) return;
        JSONObject field=imk.getField();
        ArrayList<String> klist=field.getKeylist();
        int len=klist.size();
        for(int i=0; i<len; i++){
            ImportedKey.ImportedField f=imk.get(klist.get(i));
            list.add("ALTER TABLE "+tabname+" DISABLE CONSTRAINT "+f.getFkeyName());
        }
        if(len>0) executeDDL(list);
    }

    public void enableTableFkReferences(String tabname) throws Exception {
        String sql=" SELECT A.CONSTRAINT_NAME, A.TABLE_NAME\n" +
                "   FROM USER_CONS_COLUMNS A\n" +
                "   JOIN USER_CONSTRAINTS C ON A.OWNER = C.OWNER\n" +
                "        AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME\n" +
                "   JOIN USER_CONSTRAINTS C_PK ON C.R_OWNER = C_PK.OWNER\n" +
                "        AND C.R_CONSTRAINT_NAME = C_PK.CONSTRAINT_NAME\n" +
                "   JOIN USER_CONS_COLUMNS B ON C_PK.OWNER = B.OWNER\n" +
                "        AND C_PK.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.POSITION = A.POSITION\n" +
                " WHERE C.CONSTRAINT_TYPE = 'R' AND C.STATUS = 'DISABLED' AND C_PK.TABLE_NAME='"+tabname+"'";
        ArrayList<String> list=new ArrayList<String>();
        JSONObject param=new JSONObject();
        param.put("printSql","false");
        session.getForResultSet().sql(sql, param, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    list.add("ALTER TABLE "+rs.getString("TABLE_NAME")+" ENABLE CONSTRAINT "+rs.getString("CONSTRAINT_NAME"));
                }
            }
        });
        if(list.size()>0) executeDDL(list);
    }

    public void disableTableFkReferences(String tabname) throws Exception {
        String sql="<rule from=\"A.USER_CONS_COLUMNS;C.USER_CONSTRAINTS;C_PK.USER_CONSTRAINTS;B.USER_CONS_COLUMNS\" autoCompletion=\"1\" /> SELECT A.CONSTRAINT_NAME, A.TABLE_NAME\n" +
                "   FROM USER_CONS_COLUMNS A\n" +
                "   JOIN USER_CONSTRAINTS C ON A.OWNER = C.OWNER\n" +
                "        AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME\n" +
                "   JOIN USER_CONSTRAINTS C_PK ON C.R_OWNER = C_PK.OWNER\n" +
                "        AND C.R_CONSTRAINT_NAME = C_PK.CONSTRAINT_NAME\n" +
                "   JOIN USER_CONS_COLUMNS B ON C_PK.OWNER = B.OWNER\n" +
                "        AND C_PK.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.POSITION = A.POSITION\n" +
                " WHERE C.CONSTRAINT_TYPE = 'R' AND C.STATUS = 'ENABLED' AND C_PK.TABLE_NAME='"+tabname+"'";
        ArrayList<String> list=new ArrayList<String>();
        JSONObject param=new JSONObject();
        param.put("printSql","false");
        session.getForResultSet().sql(sql, param, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                while(rs.next()){
                    list.add("ALTER TABLE "+rs.getString("TABLE_NAME")+" DISABLE CONSTRAINT "+rs.getString("CONSTRAINT_NAME"));
                }
            }
        });
        if(list.size()>0) executeDDL(list);
    }

    public void truncateTable(String tabname) throws Exception {
        disableTableFkReferences(tabname);
        cn.update("TRUNCATE TABLE "+tabname);
    }


}
